create  table jms_dm.dm_terminal_sign_summary_send_new_dt
(
end_agent_code  varchar(100) COMMENT '末端网点所属代理区编码' ,
end_franchisee_code  varchar(100) COMMENT '末端网点所属加盟商编码' ,
final_sign_network_code  varchar(100) COMMENT '最终签收派件网点编码' ,
end_center_send_date  date COMMENT '末端中心/集散发件日期' ,
order_source_name  varchar(100) COMMENT '订单来源名称' ,
goods_type_code  varchar(100) COMMENT '物品类型编码' ,
end_manage_region_code  varchar(100) COMMENT '末端网点所属管理大区编码' ,
end_manage_region_name  varchar(100) COMMENT '末端网点所属管理大区名称' ,
end_agent_name  varchar(100) COMMENT '末端网点所属代理区名称' ,
end_provider_id  varchar(100) COMMENT '末端网点所属省份id' ,
end_provider_name  varchar(100) COMMENT '末端网点所属省份名称' ,
end_city_id  varchar(100) COMMENT '末端网点所属城市id' ,
end_city_name  varchar(100) COMMENT '末端网点所属城市名称' ,
end_area_id  varchar(100) COMMENT '末端网点所属区县id' ,
end_area_name  varchar(100) COMMENT '末端网点所属区县名称' ,
end_center_code  varchar(100) COMMENT '末端发件中心/集散编码' ,
end_center_name  varchar(100) COMMENT '末端发件中心/集散名称' ,
end_franchisee_name  varchar(100) COMMENT '末端网点所属加盟商名称' ,
final_sign_network_name  varchar(100) COMMENT '最终签收派件网点名称' ,
planned_delivery_time  varchar(100) COMMENT '规划发件时间' ,
deadline_signing_time  varchar(100) COMMENT '规划签收截止时间' ,
span_days  varchar(100) COMMENT '加时天数' ,
deliver_shift  int(12) COMMENT '派件派仓班次' ,
final_sign_user_code  varchar(100) COMMENT '最后签收派件员编码' ,
final_sign_user_name  varchar(100) COMMENT '最后签收派件员名称' ,
need_sign_count  bigint COMMENT '应签收汇总' ,
aging_sign_in_time_network  bigint COMMENT '时效签收准点网点签收' ,
aging_sign_in_time_terminal_pdd  bigint COMMENT '时效签收准点终端入库(桃花岛认证)' ,
aging_sign_in_time_terminal_other  bigint COMMENT '时效签收准点终端入库(其他)' ,
aging_sign_in_time_proxy  bigint COMMENT '时效签收准点代理点收入' ,
aging_sign_in_time_transfer  bigint COMMENT '时效签收准点转邮' ,
aging_sign_in_time_count  bigint COMMENT '时效签收准点汇总' ,
22_aging_sign_over_time_network  bigint COMMENT '时效签收延误网点签收' ,
22_aging_sign_over_time_terminal_pdd  bigint COMMENT '时效签收延误终端入库(桃花岛认证)' ,
22_aging_sign_over_time_terminal_other  bigint COMMENT '时效签收延误终端入库(其他)' ,
22_aging_sign_over_time_proxy  bigint COMMENT '时效签收延误代理点收入' ,
22_aging_sign_over_time_difficult  bigint COMMENT '时效签收延误问题件' ,
22_aging_sign_over_time_other  bigint COMMENT '时效签收延误其他' ,
22_aging_sign_over_time_transfer  bigint COMMENT '时效签收延误转邮' ,
22_aging_sign_over_time_sign_count  bigint COMMENT '时效签收延误已签收汇总' ,
22_aging_sign_over_time_nosign_count  bigint COMMENT '时效签收延误未签收汇总' ,
24_aging_sign_over_time_network  bigint COMMENT '时效签收延误网点签收' ,
24_aging_sign_over_time_terminal_pdd  bigint COMMENT '时效签收延误终端入库(桃花岛认证)' ,
24_aging_sign_over_time_terminal_other  bigint COMMENT '时效签收延误终端入库(其他)' ,
24_aging_sign_over_time_proxy  bigint COMMENT '时效签收延误代理点收入' ,
24_aging_sign_over_time_difficult  bigint COMMENT '时效签收延误问题件' ,
24_aging_sign_over_time_other  bigint COMMENT '时效签收延误其他' ,
24_aging_sign_over_time_transfer  bigint COMMENT '时效签收延误转邮' ,
24_aging_sign_over_time_sign_count  bigint COMMENT '时效签收延误已签收汇总' ,
24_aging_sign_over_time_nosign_count  bigint COMMENT '时效签收延误未签收汇总' ,
00_aging_sign_over_time_network  bigint COMMENT '时效签收延误网点签收' ,
00_aging_sign_over_time_terminal_pdd  bigint COMMENT '时效签收延误终端入库(桃花岛认证)' ,
00_aging_sign_over_time_terminal_other  bigint COMMENT '时效签收延误终端入库(其他)' ,
00_aging_sign_over_time_proxy  bigint COMMENT '时效签收延误代理点收入' ,
00_aging_sign_over_time_difficult  bigint COMMENT '时效签收延误问题件' ,
00_aging_sign_over_time_other  bigint COMMENT '时效签收延误其他' ,
00_aging_sign_over_time_transfer  bigint COMMENT '时效签收延误转邮' ,
00_aging_sign_over_time_sign_count  bigint COMMENT '时效签收延误已签收汇总' ,
00_aging_sign_over_time_nosign_count  bigint COMMENT '时效签收延误未签收汇总' ,
aging_sign_24_count  bigint COMMENT '时效签收24点前签收汇总' ,
actual_sign_in_time_network  bigint COMMENT '实际签收准点网点签收' ,
actual_sign_in_time_terminal  bigint COMMENT '实际签收准点驿站/快递柜' ,
actual_sign_in_time_proxy  bigint COMMENT '实际签收准点代理点收入' ,
actual_sign_in_time_count  bigint COMMENT '实际签收准点汇总' ,
actual_sign_over_time_network  bigint COMMENT '实际签收延误网点签收' ,
actual_sign_over_time_terminal  bigint COMMENT '实际签收延误驿站/快递柜' ,
actual_sign_over_time_proxy  bigint COMMENT '实际签收延误代理点收入' ,
actual_sign_over_time_difficult  bigint COMMENT '实际签收延误问题件' ,
actual_sign_over_time_other  bigint COMMENT '实际签收延误其他' ,
actual_sign_over_time_sign_count  bigint COMMENT '实际签收延误已签收汇总' ,
actual_sign_over_time_nosign_count  bigint COMMENT '实际签收延误未签收汇总' ,
actual_sign_24_count  bigint COMMENT '实际签收24点前签收汇总' ,
aging_sign_00_count  bigint COMMENT '时效签收24点前签收汇总' ,
aging_sign_22_count  bigint COMMENT '时效签收24点前签收汇总' ,
is_common_network  int(6) COMMENT '是否共配网点',
express_type_code varchar(100) comment '产品类型编码',
express_type_name varchar(100) comment '产品类型名称',
 22_actual_sign_in_time_network int comment'22点前实际签收准点-网点签收'
,22_actual_sign_in_time_terminal int comment'22点前实际签收准点-驿站/快递柜'
,22_actual_sign_in_time_proxy int comment'22点前实际签收准点-代理点收入'
,22_actual_sign_in_time_count int comment'22点前实际签收准点-汇总'
,22_actual_sign_over_time_network int comment'22点前实际签收延误-网点签收'
,22_actual_sign_over_time_terminal int comment'22点前实际签收延误-驿站/快递柜'
,22_actual_sign_over_time_proxy int comment'22点前实际签收延误-代理点收入'
,22_actual_sign_over_time_difficult int comment'22点前实际签收延误-问题件'
,22_actual_sign_over_time_other int comment'22点前实际签收延误-其他'
,22_actual_sign_over_time_sign_count int comment'22点前实际签收延误-已签收汇总'
,22_actual_sign_over_time_nosign_count int comment'22点前实际签收延误-未收汇总'
,24_actual_sign_in_time_network int comment'22-24点实际签收准点-网点签收'
,24_actual_sign_in_time_terminal int comment'22-24点实际签收准点-驿站/快递柜'
,24_actual_sign_in_time_proxy int comment'22-24点实际签收准点-代理点收入'
,24_actual_sign_in_time_count int comment'22-24点实际签收准点-汇总'
,24_actual_sign_over_time_network int comment'22-24点实际签收延误-网点签收'
,24_actual_sign_over_time_terminal int comment'22-24点实际签收延误-驿站/快递柜'
,24_actual_sign_over_time_proxy int comment'22-24点实际签收延误-代理点收入'
,24_actual_sign_over_time_difficult int comment'22-24点实际签收延误-问题件'
,24_actual_sign_over_time_other int comment'22-24点实际签收延误-其他'
,24_actual_sign_over_time_sign_count int comment'22-24点实际签收延误-已签收汇总'
,24_actual_sign_over_time_nosign_count int comment'22-2424点实际签收延误-未收汇总'
,00_actual_sign_in_time_network int comment'超24点实际签收准点-网点签收'
,00_actual_sign_in_time_terminal int comment'超24点实际签收准点-驿站/快递柜'
,00_actual_sign_in_time_proxy int comment'超24点实际签收准点-代理点收入'
,00_actual_sign_in_time_count int comment'超24点实际签收准点-汇总'
,00_actual_sign_over_time_network int comment'超24点实际签收延误-网点签收'
,00_actual_sign_over_time_terminal int comment'超24点实际签收延误-驿站/快递柜'
,00_actual_sign_over_time_proxy int comment'超24点实际签收延误-代理点收入'
,00_actual_sign_over_time_difficult int comment'超24点实际签收延误-问题件'
,00_actual_sign_over_time_other int comment'超24点实际签收延误-其他'
,00_actual_sign_over_time_sign_count int comment'超24点实际签收延误-已签收汇总'
,00_actual_sign_over_time_nosign_count int comment'超24点实际签收延误-未收汇总'
,actual_sign_22_count int comment'22点签收量'
,actual_sign_00_count int comment'24点前前签收量'
,third_code varchar(100) comment '三段码code',
,third_name varchar(100) comment '三段码名字'
)
ENGINE=OLAP
DUPLICATE KEY(end_agent_code,end_franchisee_code,final_sign_network_code,end_center_send_date)
comment '末端时效规划签收维度汇总'
PARTITION BY RANGE (end_center_send_date) (
   START ("2022-07-30") END ("2022-09-28") EVERY (INTERVAL 1 day))DISTRIBUTED BY HASH(final_sign_user_code) BUCKETS 10
PROPERTIES (
"replication_num" = "3",
"dynamic_partition.enable" = "true",
"dynamic_partition.time_unit" = "DAY",
"dynamic_partition.time_zone" = "Asia/Shanghai",
"dynamic_partition.start" = "-365",
"dynamic_partition.end" = "3",
"dynamic_partition.prefix" = "p",
"dynamic_partition.buckets" = "10",
"in_memory" = "false",
"storage_format" = "V2"
);

-- doris 发件维度汇总增加产品类型字段的

   alter table jms_dm.dm_terminal_sign_summary_send_new_dt add column (
     end_brand_code varchar(100) comment '共配code',
     end_brand_name varchar(100) comment '共配名字'
  );